package com.glowdayz.glowmee.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.DateTime;
import com.glowdayz.common.PageHandler;
import com.glowdayz.common.MultipartBox.FileInfo;
import com.glowdayz.glowmee.cmd.PouchCMD;
import com.glowdayz.glowmee.cmd.PouchProductMapCMD;
import com.glowdayz.glowmee.cmd.ProductCMD;

/** 파우치 기능을 구현하는 클래스 */
public class PouchDAO {
	
static Logger logger = Logger.getLogger(PouchDAO.class);
	
	private static PouchDAO instance = null;
	
	DBHandler db = null;
	
	public static PouchDAO getInstance() {
		if (instance == null) {
			instance = new PouchDAO();
		}
		return instance;
	}
	
	public PouchDAO() {
		db = DBHandler.getInstance();
	}
	
	/**
	 * 파우치 전시여부
	 * @param cmd
	 * @return int
	 */
	public int isDisplayPouch(PouchCMD cmd) throws SQLException {
		logger.debug("[isDisplayPouch] ---------- Method Start ----------");
		logger.debug("[isDisplayPouch][PARAM] PouchCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE pouch "
				+ "SET isDisplay=?, modified_date=? "
				+ "WHERE idPouch=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIsDisplay());
			pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(3, cmd.getIdPouch());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[isDisplayPouch] " + e.getLocalizedMessage());
			logger.error("[isDisplayPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[isDisplayPouch][RETURN] result=" + result);
		logger.debug("[isDisplayPouch] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 오늘의 파우치 수정
	 * @param cmd
	 * @return int
	 */
	public int isTodayPouch(int id, int isToday) throws SQLException {
		logger.debug("[isTodayPouch] ---------- Method Start ----------");
		
		int result = 0;
		
		String sql = "UPDATE pouch "
				+ "SET todayPouch=?, modified_date=? "
				+ "WHERE idPouch=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, isToday);
			pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(3, id);
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[isTodayPouch] " + e.getLocalizedMessage());
			logger.error("[isTodayPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[isTodayPouch][RETURN] result=" + result);
		logger.debug("[isTodayPouch] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 에디터의픽 등록
	 * @param cmd
	 * @return int
	 */
	public int insertEditerPick(PouchCMD cmd) throws SQLException {
		logger.debug("[insertEditerPouchBanner] ---------- Method Start ----------");
		logger.debug("[insertEditerPouchBanner][PARAM] PouchCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE pouch "
				+ "SET editerPick=1, start_date=?, end_date=?, modified_date=? "
				+ "WHERE idPouch=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.getStart_date());
			pstmt.setString(2, cmd.getEnd_date());
			pstmt.setString(3, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(4, cmd.getIdPouch());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertEditerPouchBanner] " + e.getLocalizedMessage());
			logger.error("[insertEditerPouchBanner] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertEditerPouchBanner][RETURN] result=" + result);
		logger.debug("[insertEditerPouchBanner] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 에디터의픽 수정
	 * @param cmd
	 * @return int
	 */
	public int updateEditerPick(PouchCMD cmd) throws SQLException {
		logger.debug("[updateEditerPick] ---------- Method Start ----------");
		logger.debug("[updateEditerPick][PARAM] BrandCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE pouch "
				+ "SET start_date=?, end_date=?, modified_date=? "
				+ "WHERE idPouch=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.getStart_date());
			pstmt.setString(2, cmd.getEnd_date());
			pstmt.setString(3, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(4, cmd.getIdPouch());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateEditerPick] " + e.getLocalizedMessage());
			logger.error("[updateEditerPick] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateEditerPick][RETURN] result=" + result);
		logger.debug("[updateEditerPick] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 에디터의픽 삭제
	 * @param delete_list
	 * @return int
	 */
	public int deletePickXml(int idPouch) throws SQLException {
		logger.debug("[deletePickXml] ---------- Method Start ----------");
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		PreparedStatement pstmt = null;
		
		String mapSql = "UPDATE pouch "
				+ "SET isDisplay=0, modified_date=? "
				+ "WHERE idPouch=?";
		pstmt = db.getPrepareStatement(mapSql);
		try {
			pstmt.setString(1, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(2, idPouch);
			result = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[deletePickXml] " + e.getLocalizedMessage());
			logger.error("[deletePickXml] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[deletePickXml][RETURN] result=" + result);
		logger.debug("[deletePickXml] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 에디터의픽 삭제
	 * @param delete_list
	 * @return int
	 */
	public int deleteEditerPick(String[] delete_list) throws SQLException {
		logger.debug("[deleteEditerPick] ---------- Method Start ----------");
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		if (delete_list.length > 0) {
			String in_cause = "(";
			for (int i=0; i<delete_list.length; i++) {
				in_cause += delete_list[i];
				if (i + 1 < delete_list.length) {
					in_cause += ",";
				}
			}
			in_cause += ")";
			
			// 게시물 데이터 삭제처리
			String delSql = "UPDATE pouch "
					+ "SET editerPick=0, start_date=null, end_date=null, modified_date=? "
					+ "WHERE editerPick=1 AND idPouch in " + in_cause;
			PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(delSql);
			
			try {
				pstmt.setString(1, DateTime.getInstance().getNowDateTimeString());
				result = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
				logger.error("[deleteEditerPick] " + e.getLocalizedMessage());
				logger.error("[deleteEditerPick] " + e.getSQLState());
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[deleteEditerPick][RETURN] result=" + result);
		logger.debug("[deleteEditerPick] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 에디터 파우치추가
	 * @param cmd
	 * @param product
	 * @return int
	 */
	public int insertEditerPouch(PouchCMD cmd, int[] product) throws Exception {
		logger.debug("[insertPouch] ---------- Method Start ----------");
		logger.debug("[insertPouch][PARAM] PouchCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		PreparedStatement pstmt = null;
		
		String sql = "INSERT INTO pouch("
				+ "idRegister, idPouchCategory, idBrand, pouchTitle, pouchText, readCount, pouchScore, recommendCount, "
				+ "isDisplay, todayPouch, editerPick, create_date"
				+ ") VALUES (?, ?, ?, ?, ?, 0, 0, 0, 1, 0, ?, ?)";
		
		pstmt = db.getPrepareStatement(sql);
		
		DBHandler.getInstance().beginTrans();
		try {
			pstmt.setInt(1, cmd.getIdRegister());
			pstmt.setInt(2, cmd.getIdPouchCategory());
			
			if (cmd.getIdBrand() < 1) {
				pstmt.setNull(3, Types.INTEGER);
			} else {
				pstmt.setInt(3, cmd.getIdBrand());
			}
			
			pstmt.setString(4, cmd.getPouchTitle());
			pstmt.setString(5, cmd.getPouchText());
			pstmt.setInt(6, cmd.getEditerPick());
			pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
			result = pstmt.executeUpdate();
			
			if (result > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			
			pstmt.close();
			pstmt = null;
			
			if (cmd.getImage() != null) {
				
				String fileSql = "INSERT INTO attachefile ("
						+ "tableName, tableKey, fileOrgName, fileSaveName, fileDir, fileSize, fileType, isDisplay, create_date, isUser"
						+ ") VALUES ('pouch', ?, ?, ?, ?, ?, ?, 1, ?, 0)";
				
				
				int fileResult = 0;
				
				FileInfo item = cmd.getImage();
				
				pstmt = db.getPrepareStatement(fileSql);
				pstmt.setInt(1, result);
				pstmt.setString(2, item.getFileOrgName());
				pstmt.setString(3, item.getFileSaveName());
				pstmt.setString(4, item.getFileDir());
				pstmt.setLong(5, item.getFileSize());
				pstmt.setString(6, item.getFileType());
				pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
				
				fileResult = pstmt.executeUpdate();
				
				if (fileResult > 0) {
					// 자동증가 일련변호 조회
					ResultSet rs = pstmt.getGeneratedKeys();
					if (rs.next()) {
						fileResult = rs.getInt(1);
					}
					rs.close();
					rs = null;
				} else {
					// 모든 데이터 저장 취소
					pstmt.close();
					pstmt = null;
					DBHandler.getInstance().rollback();
					return -1;
				}
				
				pstmt.close();
				pstmt = null;
					
				
				String update = "UPDATE pouch "
						+ "SET pouchImg=? "
						+ "WHERE idPouch=?";
				pstmt = db.getPrepareStatement(update);
				pstmt.setInt(1, fileResult);
				pstmt.setInt(2, result);
				pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			}
			
			boolean first = true;
			
			for (int i=0; i<10; i++) {
				if (product[i] == -1) {
					String productSql = "INSERT INTO pouchproductmapping ("
							+ "idPouch, create_date"
							+ ") VALUES (?, ?)";
					
					pstmt = db.getPrepareStatement(productSql);
					pstmt.setInt(1, result);
					pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				} else {
					String productSql = "INSERT INTO pouchproductmapping ("
							+ "idPouch, idProduct, create_date"
							+ ") VALUES (?, ?, ?)";
					
					pstmt = db.getPrepareStatement(productSql);
					pstmt.setInt(1, result);
					pstmt.setInt(2, product[i]);
					pstmt.setString(3, DateTime.getInstance().getNowDateTimeString());
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
					
					if (first && cmd.getIdBrand() > 0) {
						String select = "SELECT idAttacheFile FROM attachefile WHERE tableName='product' AND tableKey=?";
						pstmt = db.getPrepareStatement(select);
						pstmt.setInt(1, product[i]);
						ResultSet rs = pstmt.executeQuery();
						int productIdx = 0;
						if (rs != null) {
							if(rs.next()) {
								productIdx = rs.getInt(1);
							}
							rs.close();
							rs = null;
						}
						pstmt.close();
						pstmt = null;
						
						String update = "UPDATE pouch "
								+ "SET pouchImg=? "
								+ "WHERE idPouch=?";
						pstmt = db.getPrepareStatement(update);
						pstmt.setInt(1, productIdx);
						pstmt.setInt(2, result);
						pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
						
						first = false;
					}
				}
			}
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertPouch] " + e.getLocalizedMessage());
			logger.error("[insertPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertPouch][RETURN] result=" + result);
		logger.debug("[insertPouch] ---------- Method End ----------");
		
		return result;
	}
	
	/** 유저 픽 추가 xml */
	public int insertUserPouch(PouchCMD cmd, int[] product, String[] requestText, ArrayList<FileInfo> files) throws Exception {
		logger.debug("[insertUserPouch] ---------- Method Start ----------");
		
		int result = 0;
		int num = 0;
		PreparedStatement pstmt = null;
		
		String cate = "SELECT idPouchCategory FROM pouchcategory WHERE pouchCategoryText=?";
		pstmt = DBHandler.getInstance().getPrepareStatement(cate);
		try {
			pstmt.setString(1, cmd.getIdPouchCategoryValue());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					num = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getUserPouch] " + e.getLocalizedMessage());
			logger.error("[getUserPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		DBHandler.getInstance().beginTrans();
		
		String sql = "INSERT INTO pouch("
				+ "idRegister, idPouchCategory, pouchTitle, pouchText, readCount, pouchScore, recommendCount, "
				+ "isDisplay, todayPouch, editerPick, create_date"
				+ ") VALUES (?, ?, ?, ?, 0, 0, 0, 1, 0, 0, ?)";
		
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIdRegister());
			pstmt.setInt(2, num);
			pstmt.setString(3, cmd.getPouchTitle());
			pstmt.setString(4, cmd.getPouchText());
			pstmt.setString(5, DateTime.getInstance().getNowDateTimeString());
			result = pstmt.executeUpdate();
			
			if (result > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			
			pstmt.close();
			pstmt = null;
			
			boolean first = true;
			
			for (int i=0; i<10; i++) {
				if (product[i] == 1) {
					String productSql = "INSERT INTO pouchproductmapping ("
							+ "idPouch, idProduct, requestText, isConfirm, create_date"
							+ ") VALUES (?, ?, ?, 0, ?)";
					pstmt = db.getPrepareStatement(productSql);
					pstmt.setInt(1, result);
					pstmt.setInt(2, product[i]);
					pstmt.setString(3, requestText[i]);
					pstmt.setString(4, DateTime.getInstance().getNowDateTimeString());
					int requestId = pstmt.executeUpdate();
					if (requestId > 0) {
						// 자동증가 일련변호 조회
						ResultSet rs = pstmt.getGeneratedKeys();
						if (rs.next()) {
							requestId = rs.getInt(1);
						}
						rs.close();
						rs = null;
					}
					pstmt.close();
					pstmt = null;
					FileInfo imgFile = null;
					
					if (files != null) {
						for (int j=0; j<files.size(); j++) {
							imgFile = files.get(j);
							String imgIdx = imgFile.getFileOrgName();
							imgIdx = imgIdx.substring(imgIdx.indexOf("_") + 1, imgIdx.indexOf("_") + 2);
							int imgId = Integer.parseInt(imgIdx);
							
							if (i + 1 == imgId) {
								String fileSql = "INSERT INTO attachefile ("
										+ "tableName, tableKey, fileOrgName, fileSaveName, fileDir, fileSize, fileType, isDisplay, create_date, isUser"
										+ ") VALUES ('pouchproductmapping', ?, ?, ?, ?, ?, ?, 1, ?, 0)";
								
								
								int fileResult = 0;
								
								pstmt = db.getPrepareStatement(fileSql);
								pstmt.setInt(1, requestId);
								pstmt.setString(2, imgFile.getFileOrgName());
								pstmt.setString(3, imgFile.getFileSaveName());
								pstmt.setString(4, imgFile.getFileDir());
								pstmt.setLong(5, imgFile.getFileSize());
								pstmt.setString(6, imgFile.getFileType());
								pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
								
								fileResult = pstmt.executeUpdate();
								
								if (fileResult > 0) {
									// 자동증가 일련변호 조회
									ResultSet rs = pstmt.getGeneratedKeys();
									if (rs.next()) {
										fileResult = rs.getInt(1);
									}
									rs.close();
									rs = null;
								} else {
									// 모든 데이터 저장 취소
									pstmt.close();
									pstmt = null;
									DBHandler.getInstance().rollback();
									return -1;
								}
								pstmt.close();
								pstmt = null;
								
								String update = "UPDATE pouchproductmapping "
										+ "SET requestImg=? "
										+ "WHERE idPouchProduct=?";
								pstmt = db.getPrepareStatement(update);
								pstmt.setInt(1, fileResult);
								pstmt.setInt(2, requestId);
								pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
							}
						}
					}
				} else if (product[i] < 1) {
					String productSql = "INSERT INTO pouchproductmapping ("
							+ "idPouch, create_date"
							+ ") VALUES (?, ?)";
					
					pstmt = db.getPrepareStatement(productSql);
					pstmt.setInt(1, result);
					pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				} else {
					String productSql = "INSERT INTO pouchproductmapping ("
							+ "idPouch, idProduct, create_date"
							+ ") VALUES (?, ?, ?)";
					
					pstmt = db.getPrepareStatement(productSql);
					pstmt.setInt(1, result);
					pstmt.setInt(2, product[i]);
					pstmt.setString(3, DateTime.getInstance().getNowDateTimeString());
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
					
					if (first) {
						String select = "SELECT idAttacheFile FROM attachefile WHERE tableName='product' AND tableKey=?";
						pstmt = db.getPrepareStatement(select);
						pstmt.setInt(1, product[i]);
						ResultSet rs = pstmt.executeQuery();
						int productIdx = 0;
						if (rs != null) {
							if(rs.next()) {
								productIdx = rs.getInt(1);
							}
							rs.close();
							rs = null;
						}
						pstmt.close();
						pstmt = null;
						
						String update = "UPDATE pouch "
								+ "SET pouchImg=? "
								+ "WHERE idPouch=?";
						pstmt = db.getPrepareStatement(update);
						pstmt.setInt(1, productIdx);
						pstmt.setInt(2, result);
						pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
						
						first = false;
					}
				}
			}
			
			if (first) {
				int productImg = 0;
				
				String select = "SELECT productImg FROM product WHERE idProduct=1";
				pstmt = db.getPrepareStatement(select);
				ResultSet rs = pstmt.executeQuery();
				if (rs != null) {
					if(rs.next()) {
						productImg = rs.getInt(1);
					}
					rs.close();
					rs = null;
				}
				pstmt.close();
				pstmt = null;
				
				if (productImg > 0) {
					String update = "UPDATE pouch "
							+ "SET pouchImg=? "
							+ "WHERE idPouch=?";
					pstmt = db.getPrepareStatement(update);
					pstmt.setInt(1, productImg);
					pstmt.setInt(2, result);
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				}
			}
			
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertUserPouch] " + e.getLocalizedMessage());
			logger.error("[insertUserPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertUserPouch][RETURN] result=" + result);
		logger.debug("[insertUserPouch] ---------- Method End ----------");
		
		return result;
	}
	
	/** 유저 픽 수정 xml */
	public int updateUserPouch(PouchCMD cmd, int[] product, String[] requestText, ArrayList<FileInfo> files) throws Exception {
		logger.debug("[updateUserPouch] ---------- Method Start ----------");
		
		int result = 0;
		int num = 0;
		PreparedStatement pstmt = null;
		
		String cate = "SELECT idPouchCategory FROM pouchcategory WHERE pouchCategoryText=?";
		pstmt = DBHandler.getInstance().getPrepareStatement(cate);
		try {
			pstmt.setString(1, cmd.getIdPouchCategoryValue());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					num = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[updateUserPouch] " + e.getLocalizedMessage());
			logger.error("[updateUserPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		DBHandler.getInstance().beginTrans();
		
		int[] beforeProduct = new int[10];
		int productCount = 0;
		
		String select = "SELECT idPouchProduct FROM pouchproductmapping WHERE idPouch=?";
		pstmt = DBHandler.getInstance().getPrepareStatement(select);
		try {
			pstmt.setInt(1, cmd.getIdPouch());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				while (rs.next()) {
					beforeProduct[productCount] = rs.getInt(1);
					productCount++;
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[updateUserPouch] " + e.getLocalizedMessage());
			logger.error("[updateUserPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		boolean first = true;
		
		for (int i=0; i<beforeProduct.length; i++) {
			try {
				if (product[i] < 1) {
					String updateMap = "UPDATE pouchproductmapping "
							+ "SET idProduct=null, requestText=null, requestImg=null, isConfirm=null, modified_date=? "
							+ "WHERE idPouchProduct=?";
					pstmt = db.getPrepareStatement(updateMap);
					pstmt.setString(1, DateTime.getInstance().getNowDateTimeString());
					pstmt.setInt(2, beforeProduct[i]);
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				} else {
					String updateMap = "UPDATE pouchproductmapping "
							+ "SET idProduct=?, requestText=null, requestImg=null, isConfirm=null, modified_date=? "
							+ "WHERE idPouchProduct=?";
					pstmt = db.getPrepareStatement(updateMap);
					pstmt.setInt(1, product[i]);
					pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
					pstmt.setInt(3, beforeProduct[i]);
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
					
					if (first) {
						String imgSql = "SELECT idAttacheFile FROM attachefile WHERE tableName='product' AND tableKey=?";
						pstmt = db.getPrepareStatement(imgSql);
						pstmt.setInt(1, product[i]);
						ResultSet rs = pstmt.executeQuery();
						int productIdx = 0;
						if (rs != null) {
							if(rs.next()) {
								productIdx = rs.getInt(1);
							}
							rs.close();
							rs = null;
						}
						pstmt.close();
						pstmt = null;
						
						String update = "UPDATE pouch "
								+ "SET pouchImg=? "
								+ "WHERE idPouch=?";
						pstmt = db.getPrepareStatement(update);
						pstmt.setInt(1, productIdx);
						pstmt.setInt(2, cmd.getIdPouch());
						pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
						
						first = false;
					}
				}
				
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
				logger.error("[updateUserPouch] " + e.getLocalizedMessage());
				logger.error("[updateUserPouch] " + e.getSQLState());
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
		}
		
		String sql = "UPDATE pouch "
				+ "SET idPouchCategory=?, pouchTitle=?, pouchText=?, modified_date=? "
				+ "WHERE idPouch=?";
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, num);
			pstmt.setString(2, cmd.getPouchTitle());
			pstmt.setString(3, cmd.getPouchText());
			pstmt.setString(4, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(5, cmd.getIdPouch());
			result = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateUserPouch] " + e.getLocalizedMessage());
			logger.error("[updateUserPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateUserPouch][RETURN] result=" + result);
		logger.debug("[updateUserPouch] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 에디터 파우치 수정
	 * @param cmd
	 * @param product
	 * @return int
	 */
	public int updateEditerPouch(PouchCMD cmd, int[] product) throws SQLException {
		logger.debug("[updateEditerPouch] ---------- Method Start ----------");
		logger.debug("[updateEditerPouch][PARAM] PouchCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		PreparedStatement pstmt = null;
		
		// (3) 게시물 정보 갱신
		String sql = "UPDATE pouch "
				+ "SET idPouchCategory=?, idBrand=?, pouchTitle=?, pouchText=?, isDisplay=?, editerPick=?, modified_date=? "
				+ "WHERE idPouch=?";
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIdPouchCategory());
			
			if (cmd.getIdBrand() < 1) {
				pstmt.setNull(2, Types.INTEGER);
			} else {
				pstmt.setInt(2, cmd.getIdBrand());
			}
			
			pstmt.setString(3, cmd.getPouchTitle());
			pstmt.setString(4, cmd.getPouchText());
			pstmt.setInt(5, cmd.getIsDisplay());
			pstmt.setInt(6, cmd.getEditerPick());
			pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(8, cmd.getIdPouch());
			
			result = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateEditerPouch] " + e.getLocalizedMessage());
			logger.error("[updateEditerPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (cmd.getImage() != null) {
			
			String fileSql = "INSERT INTO attachefile ("
					+ "tableName, tableKey, fileOrgName, fileSaveName, fileDir, fileSize, fileType, isDisplay, create_date, isUser"
					+ ") VALUES ('pouch', ?, ?, ?, ?, ?, ?, 1, ?, 0)";
			
			int fileResult = 0;
			
			FileInfo item = cmd.getImage();
			
			pstmt = db.getPrepareStatement(fileSql);
			pstmt.setInt(1, cmd.getIdPouch());
			pstmt.setString(2, item.getFileOrgName());
			pstmt.setString(3, item.getFileSaveName());
			pstmt.setString(4, item.getFileDir());
			pstmt.setLong(5, item.getFileSize());
			pstmt.setString(6, item.getFileType());
			pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
			
			fileResult = pstmt.executeUpdate();
			
			if (fileResult > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					fileResult = rs.getInt(1);
				}
				rs.close();
				rs = null;
			} else {
				// 모든 데이터 저장 취소
				pstmt.close();
				pstmt = null;
				DBHandler.getInstance().rollback();
				return -1;
			}
			pstmt.close();
			pstmt = null;
			
			String update = "UPDATE pouch "
					+ "SET pouchImg=? "
					+ "WHERE idPouch=?";
			pstmt = db.getPrepareStatement(update);
			pstmt.setInt(1, fileResult);
			pstmt.setInt(2, cmd.getIdPouch());
			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		}
		
		if (cmd.getIdBrand() < 1) {
			String select = "SELECT idAttacheFile FROM attachefile WHERE tableName='pouch' AND tableKey=?";
			pstmt = db.getPrepareStatement(select);
			pstmt.setInt(1, cmd.getIdPouch());
			ResultSet rs = pstmt.executeQuery();
			int pouchIdx = 0;
			if (rs != null) {
				if(rs.next()) {
					pouchIdx = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
			
			String update = "UPDATE pouch "
					+ "SET pouchImg=? "
					+ "WHERE idPouch=?";
			pstmt = db.getPrepareStatement(update);
			pstmt.setInt(1, pouchIdx);
			pstmt.setInt(2, cmd.getIdPouch());
			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		}
		
		sql = "SELECT idPouchProduct FROM pouchproductmapping WHERE idPouch=?";
		pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		int[] getProduct = new int[10];
		try {
			pstmt.setInt(1, cmd.getIdPouch());
			ResultSet rs = pstmt.executeQuery();
			int count = 0;
			if (rs != null) {
				while(rs.next()) {
					int idProduct = rs.getInt(1);
					getProduct[count] = idProduct;
					count++;
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateEditerPouch] " + e.getLocalizedMessage());
			logger.error("[updateEditerPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		try {
			boolean first = true;
			
			for (int i=0; i<10; i++) {
				if (product[i] == -1) {
					String productSql = "UPDATE pouchproductmapping "
							+ "SET idProduct=null, modified_date=? "
							+ "WHERE idPouchProduct=?";
					pstmt = db.getPrepareStatement(productSql);
					pstmt.setString(1, DateTime.getInstance().getNowDateTimeString());
					pstmt.setInt(2, getProduct[i]);
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				} else {
					String productSql = "UPDATE pouchproductmapping "
							+ "SET idProduct=?, modified_date=? "
							+ "WHERE idPouchProduct=?";
					pstmt = db.getPrepareStatement(productSql);
					pstmt.setInt(1, product[i]);
					pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
					pstmt.setInt(3, getProduct[i]);
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
					
					if (first && cmd.getIdBrand() > 0) {
						String select = "SELECT idAttacheFile FROM attachefile WHERE tableName='product' AND tableKey=?";
						pstmt = db.getPrepareStatement(select);
						pstmt.setInt(1, product[i]);
						ResultSet rs = pstmt.executeQuery();
						int productIdx = 0;
						if (rs != null) {
							if(rs.next()) {
								productIdx = rs.getInt(1);
							}
							rs.close();
							rs = null;
						}
						pstmt.close();
						pstmt = null;
						
						String update = "UPDATE pouch "
								+ "SET pouchImg=? "
								+ "WHERE idPouch=?";
						pstmt = db.getPrepareStatement(update);
						pstmt.setInt(1, productIdx);
						pstmt.setInt(2, cmd.getIdPouch());
						pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
						
						first = false;
					}
				}
			}
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateEditerPouch] " + e.getLocalizedMessage());
			logger.error("[updateEditerPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateEditerPouch][RETURN] result=" + result);
		logger.debug("[updateEditerPouch] ---------- Method End ----------");
		
		return result;
	}
	
	/** 유저 파우치 수정 */
	public int updateUserPouch(PouchCMD cmd, int[] product) throws SQLException {
		logger.debug("[updateUserPouch] ---------- Method Start ----------");
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		PreparedStatement pstmt = null;
		
		// (3) 게시물 정보 갱신
		String sql = "UPDATE pouch "
				+ "SET idPouchCategory=?, pouchTitle=?, pouchText=?, isDisplay=?, modified_date=? "
				+ "WHERE idPouch=?";
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIdPouchCategory());
			pstmt.setString(2, cmd.getPouchTitle());
			pstmt.setString(3, cmd.getPouchText());
			pstmt.setInt(4, cmd.getIsDisplay());
			pstmt.setString(5, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(6, cmd.getIdPouch());
			
			result = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateUserPouch] " + e.getLocalizedMessage());
			logger.error("[updateUserPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		sql = "SELECT idPouchProduct FROM pouchproductmapping WHERE idPouch=?";
		pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		int[] getProduct = new int[10];
		try {
			pstmt.setInt(1, cmd.getIdPouch());
			ResultSet rs = pstmt.executeQuery();
			int count = 0;
			if (rs != null) {
				while(rs.next()) {
					int idProduct = rs.getInt(1);
					getProduct[count] = idProduct;
					count++;
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateUserPouch] " + e.getLocalizedMessage());
			logger.error("[updateUserPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		try {
			boolean first = true;
			
			for (int i=0; i<10; i++) {
				if (product[i] < 1) {
					String productSql = "UPDATE pouchproductmapping "
							+ "SET idProduct=null, modified_date=? "
							+ "WHERE idPouchProduct=?";
					pstmt = db.getPrepareStatement(productSql);
					pstmt.setString(1, DateTime.getInstance().getNowDateTimeString());
					pstmt.setInt(2, getProduct[i]);
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				} else {
					String productSql = "UPDATE pouchproductmapping "
							+ "SET idProduct=?, modified_date=? "
							+ "WHERE idPouchProduct=?";
					pstmt = db.getPrepareStatement(productSql);
					pstmt.setInt(1, product[i]);
					pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
					pstmt.setInt(3, getProduct[i]);
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
					
					if (first) {
						String select = "SELECT idAttacheFile FROM attachefile WHERE tableName='product' AND tableKey=?";
						pstmt = db.getPrepareStatement(select);
						pstmt.setInt(1, product[i]);
						ResultSet rs = pstmt.executeQuery();
						int productIdx = 0;
						if (rs != null) {
							if(rs.next()) {
								productIdx = rs.getInt(1);
							}
							rs.close();
							rs = null;
						}
						pstmt.close();
						pstmt = null;
						
						String update = "UPDATE pouch "
								+ "SET pouchImg=? "
								+ "WHERE idPouch=?";
						pstmt = db.getPrepareStatement(update);
						pstmt.setInt(1, productIdx);
						pstmt.setInt(2, cmd.getIdPouch());
						pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
						
						first = false;
					}
				}
			}
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateUserPouch] " + e.getLocalizedMessage());
			logger.error("[updateUserPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateUserPouch][RETURN] result=" + result);
		logger.debug("[updateUserPouch] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 에디터파우치 조회
	 * @param idPouch
	 * @return PouchCMD
	 */
	public PouchCMD getEditerPouch(int idPouch) throws SQLException {
		logger.debug("[getEditerPouch] ---------- Method Start ----------");
		logger.debug("[getEditerPouch][PARAM] idPouch=" + idPouch);
		
		PouchCMD result = null;
		String sql = "SELECT idPouch, idPouchCategory, idBrand, pouchTitle, pouchText, recommendCount, isDisplay, editerPick, pouchImg, create_date, "
				+ "(SELECT COUNT(*) FROM pouchcomment c WHERE pouch.idPouch=c.idPouch) count "
				+ "FROM pouch "
				+ "WHERE idPouch=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idPouch);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					int idPouchCategory = rs.getInt(2);
					int idBrand = rs.getInt(3);
					String pouchTitle = rs.getString(4);
					String pouchText = rs.getString(5);
					int isDisplay =rs.getInt(6);
					int recommendCount =rs.getInt(7);
					int editerPick = rs.getInt(8);
					int pouchImg = rs.getInt(9);
					String create_date = rs.getString(10);
					int count = rs.getInt(11);
					
					FileInfo image = this.getImage(num);
					ArrayList<PouchProductMapCMD> list = this.getPouchProductMap(num);
					
					result = new PouchCMD(num, idPouchCategory, pouchTitle, pouchText, isDisplay, recommendCount, editerPick, pouchImg, 
							create_date, image, list, count, idBrand);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getEditerPouch] " + e.getLocalizedMessage());
			logger.error("[getEditerPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getEditerPouch][RETURN] result=" + result.toString());
		}
		logger.debug("[getEditerPouch] ---------- Method End ----------");
		
		return result;
	}
	
	public PouchCMD getUserPouch(int idPouch) throws SQLException {
		logger.debug("[getUserPouch] ---------- Method Start ----------");
		logger.debug("[getUserPouch][PARAM] idPouch=" + idPouch);
		
		PouchCMD result = null;
		String sql = "SELECT p.idPouch, p.idPouchCategory, p.pouchTitle, p.pouchText, p.isDisplay, p.editerPick, p.pouchImg, p.create_date, "
				+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count, p.recommendCount, r.nickName "
				+ "FROM pouch p, register r "
				+ "WHERE p.idRegister=r.idRegister AND idPouch=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idPouch);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					int idPouchCategory = rs.getInt(2);
					String pouchTitle = rs.getString(3);
					String pouchText = rs.getString(4);
					int isDisplay =rs.getInt(5);
					int editerPick = rs.getInt(6);
					int pouchImg = rs.getInt(7);
					String create_date = rs.getString(8);
					int count = rs.getInt(9);
					int recommendCount = rs.getInt(10);
					String nickName = rs.getString(11);
					
					ArrayList<PouchProductMapCMD> list = this.getPouchProductMap(num);
					
					result = new PouchCMD(num, idPouchCategory, pouchTitle, pouchText, recommendCount, isDisplay, editerPick, pouchImg, 
							create_date, list, count, nickName);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getUserPouch] " + e.getLocalizedMessage());
			logger.error("[getUserPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getUserPouch][RETURN] result=" + result.toString());
		}
		logger.debug("[getUserPouch] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 에디터파우치 리스트
	 * @param page
	 * @param count
	 * @param order
	 * @param where
	 * @throws SQLException 
	 */
	public ArrayList<PouchCMD> getEditerPouchList(int page, int count, String order, String where) throws SQLException {
		logger.debug("[getEditerPouchList] ---------- Method Start ----------");
		logger.debug("[getEditerPouchList][PARAM] page=" + page);
		logger.debug("[getEditerPouchList][PARAM] count=" + count);
		logger.debug("[getEditerPouchList][PARAM] order=" + order);
		logger.debug("[getEditerPouchList][PARAM] where=" + where);
		
		ArrayList<PouchCMD> result = null;
		int cnt;
		if (where == null) {
			cnt = db.getCount("pouch p, register r", "p.idPouch", "p.idRegister=r.idRegister AND r.userType='ADMIN'");
		} else {
			cnt = db.getCount("pouch p, register r", "p.idPouch", "p.idRegister=r.idRegister AND r.userType='ADMIN'" + where);
		}
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = null;
		if (order == null && where == null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.editerPick, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND r.userType='ADMIN' AND p.idPouchCategory=pc.idPouchCategory "
					+ "ORDER BY p.create_date DESC "
					+ "LIMIT ?, ?";
		} else if (order != null && where != null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.editerPick, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND r.userType='ADMIN' AND p.idPouchCategory=pc.idPouchCategory" + where + " "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (order != null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.editerPick, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND r.userType='ADMIN' AND p.idPouchCategory=pc.idPouchCategory "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (where != null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.editerPick, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND r.userType='ADMIN' AND p.idPouchCategory=pc.idPouchCategory" + where + " "
					+ "ORDER BY p.create_date DESC "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<PouchCMD>();
				
				while(rs.next()) {
					int idPouch = rs.getInt(1);
					int idPouchCategory = rs.getInt(2);
					String pouchCategoryText = rs.getString(3);
					int pouchImg = rs.getInt(4);
					String pouchTitle = rs.getString(5);
					String create_date = rs.getString(6);
					int productCount = rs.getInt(7);
					int editerPick = rs.getInt(8);
					int recommendCount = rs.getInt(9);
					int commentCount = rs.getInt(10);
					int isDisplay = rs.getInt(11);
					
					FileInfo image = this.getImage4(pouchImg);
					
					PouchCMD item = new PouchCMD(idPouch, idPouchCategory, pouchCategoryText, pouchTitle, recommendCount, 
							isDisplay, editerPick, pouchImg, create_date, image, productCount, commentCount);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getEditerPouchList] " + e.getLocalizedMessage());
			logger.error("[getEditerPouchList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getEditerPouchList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getEditerPouchList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 파우치 리스트 */
	public ArrayList<PouchCMD> getPouchList(int page, int count, String order, String where) throws SQLException {
		logger.debug("[getEditerPouchList] ---------- Method Start ----------");
		logger.debug("[getEditerPouchList][PARAM] page=" + page);
		logger.debug("[getEditerPouchList][PARAM] count=" + count);
		logger.debug("[getEditerPouchList][PARAM] order=" + order);
		logger.debug("[getEditerPouchList][PARAM] where=" + where);
		
		ArrayList<PouchCMD> result = null;
		int cnt;
		if (where == null) {
			cnt = db.getCount("pouch p, register r", "p.idPouch", "p.idRegister=r.idRegister AND p.isDisplay=1");
		} else {
			cnt = db.getCount("pouch p, register r", "p.idPouch", "p.idRegister=r.idRegister AND p.isDisplay=1" + where);
		}
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = null;
		if (order == null && where == null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.editerPick, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND p.isDisplay=1 AND p.idPouchCategory=pc.idPouchCategory "
					+ "ORDER BY p.create_date DESC "
					+ "LIMIT ?, ?";
		} else if (order != null && where != null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.editerPick, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND p.isDisplay=1 AND p.idPouchCategory=pc.idPouchCategory" + where + " "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (order != null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.editerPick, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND p.isDisplay=1 AND p.idPouchCategory=pc.idPouchCategory "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (where != null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.editerPick, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND p.isDisplay=1 AND p.idPouchCategory=pc.idPouchCategory" + where + " "
					+ "ORDER BY p.create_date DESC "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<PouchCMD>();
				
				while(rs.next()) {
					int idPouch = rs.getInt(1);
					int idPouchCategory = rs.getInt(2);
					String pouchCategoryText = rs.getString(3);
					int pouchImg = rs.getInt(4);
					String pouchTitle = rs.getString(5);
					String create_date = rs.getString(6);
					int productCount = rs.getInt(7);
					int editerPick = rs.getInt(8);
					int recommendCount = rs.getInt(9);
					int commentCount = rs.getInt(10);
					int isDisplay = rs.getInt(11);
					
					FileInfo image = this.getImage(idPouch);
					
					PouchCMD item = new PouchCMD(idPouch, idPouchCategory, pouchCategoryText, pouchTitle, recommendCount, 
							isDisplay, editerPick, pouchImg, create_date, image, productCount, commentCount);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getEditerPouchList] " + e.getLocalizedMessage());
			logger.error("[getEditerPouchList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getEditerPouchList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getEditerPouchList] ---------- Method End ----------");
		
		return result;
	}
	
	public ArrayList<PouchCMD> getUserPouchList(int page, int count, String order, String where) throws SQLException {
		logger.debug("[getUserPouchList] ---------- Method Start ----------");
		logger.debug("[getUserPouchList][PARAM] page=" + page);
		logger.debug("[getUserPouchList][PARAM] count=" + count);
		logger.debug("[getUserPouchList][PARAM] order=" + order);
		logger.debug("[getUserPouchList][PARAM] where=" + where);
		
		ArrayList<PouchCMD> result = null;
		int cnt;
		if (where == null) {
			cnt = db.getCount("pouch p, register r", "p.idPouch", "p.idRegister=r.idRegister AND r.userType='USER'");
		} else {
			cnt = db.getCount("pouch p, register r", "p.idPouch", "p.idRegister=r.idRegister AND r.userType='USER'" + where);
		}
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = null;
		if (order == null && where == null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.idRegister, r.nickName, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND r.userType='USER' AND p.idPouchCategory=pc.idPouchCategory "
					+ "GROUP BY p.idPouch ORDER BY p.create_date DESC "
					+ "LIMIT ?, ?";
		} else if (order != null && where != null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.idRegister, r.nickName, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND r.userType='USER' AND p.idPouchCategory=pc.idPouchCategory" + where + " "
					+ "GROUP BY p.idPouch ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (order != null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.idRegister, r.nickName, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND r.userType='USER' AND p.idPouchCategory=pc.idPouchCategory "
					+ "GROUP BY p.idPouch ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (where != null) {
			sql = "SELECT p.idPouch, p.idPouchCategory, pc.pouchCategoryText, p.pouchImg, p.pouchTitle, p.create_date, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.idRegister, r.nickName, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.isDisplay "
					+ "FROM pouch p, register r, pouchcategory pc "
					+ "WHERE p.idRegister=r.idRegister AND r.userType='USER' AND p.idPouchCategory=pc.idPouchCategory" + where + " "
					+ "GROUP BY p.idPouch ORDER BY p.create_date DESC "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<PouchCMD>();
				
				while(rs.next()) {
					int idPouch = rs.getInt(1);
					int idPouchCategory = rs.getInt(2);
					String pouchCategoryText = rs.getString(3);
					int pouchImg = rs.getInt(4);
					String pouchTitle = rs.getString(5);
					String create_date = rs.getString(6);
					int productCount = rs.getInt(7);
					int idRegister = rs.getInt(8);
					String nickName = rs.getString(9);
					int recommendCount = rs.getInt(10);
					int commentCount = rs.getInt(11);
					int isDisplay = rs.getInt(12);
					
					FileInfo image = this.getImage4(pouchImg);
					
					PouchCMD item = new PouchCMD(idPouch, idPouchCategory, pouchCategoryText, pouchTitle, recommendCount, 
							isDisplay, idRegister, nickName, pouchImg, create_date, image, productCount, commentCount);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getUserPouchList] " + e.getLocalizedMessage());
			logger.error("[getUserPouchList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getUserPouchList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getUserPouchList] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 오늘의 파우치 리스트
	 * @param page
	 * @param count
	 * @return ArrayList<PouchCMD>
	 */
	public ArrayList<PouchCMD> getTodayPouchList(int page, int count, String where) throws SQLException {
		logger.debug("[getTodayPouchList] ---------- Method Start ----------");
		logger.debug("[getTodayPouchList][PARAM] page=" + page);
		logger.debug("[getTodayPouchList][PARAM] count=" + count);
		
		ArrayList<PouchCMD> result = null;
		
		int cnt;
		if (where == null) {
			cnt = db.getCount("pouch", "idPouch", "todayPouch=1");
		} else {
			cnt = db.getCount("pouch p, register r", "p.idPouch", "p.idRegister=r.idRegister AND p.todayPouch=0" + where);
		}
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = null;
		if (where == null) {
			sql = "SELECT p.idPouch, p.pouchImg, p.pouchTitle, p.create_date, r.userType, r.nickName, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.isDisplay "
					+ "FROM pouch p, register r "
					+ "WHERE p.todayPouch=1 AND p.idRegister=r.idRegister "
					+ "ORDER BY p.create_date DESC "
					+ "LIMIT ?, ?";
		} else {
			sql = "SELECT p.idPouch, p.pouchImg, p.pouchTitle, p.create_date, r.userType, r.nickName, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.isDisplay "
					+ "FROM pouch p, register r "
					+ "WHERE p.todayPouch=0 AND p.idRegister=r.idRegister" + where + " "
					+ "ORDER BY p.create_date DESC "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<PouchCMD>();
				
				while(rs.next()) {
					int idPouch = rs.getInt(1);
					int pouchImg = rs.getInt(2);
					String pouchTitle = rs.getString(3);
					String create_date = rs.getString(4);
					String userType = rs.getString(5);
					String nickName = rs.getString(6);
					int count1 = rs.getInt(7);
					int isDisplay = rs.getInt(8);
					
					FileInfo image = this.getImage(idPouch);
					
					PouchCMD item = new PouchCMD(idPouch, nickName, userType, pouchTitle, isDisplay, pouchImg, create_date, image, count1);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getTodayPouchList] " + e.getLocalizedMessage());
			logger.error("[getTodayPouchList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getTodayPouchList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getTodayPouchList] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 오늘의 파우치 xml
	 * @param count
	 * @param where
	 * @return
	 */
	public ArrayList<PouchCMD> getTodayPouchList(int count, String where) throws SQLException {
		logger.debug("[getTodayPouchList] ---------- Method Start ----------");
		logger.debug("[getTodayPouchList][PARAM] count=" + count);
		
		ArrayList<PouchCMD> result = null;
		
		int cnt;
		if (where == null) {
			cnt = db.getCount("pouch", "idPouch", "todayPouch=1");
		} else {
			cnt = db.getCount("pouch p, register r", "p.idPouch", "p.idRegister=r.idRegister AND p.todayPouch=0" + where);
		}
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = null;
		if (where == null) {
			sql = "SELECT p.idPouch, p.pouchImg, p.pouchTitle, p.create_date, r.userType, r.nickName, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.isDisplay, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2 "
					+ "FROM pouch p, register r "
					+ "WHERE p.todayPouch=1 AND p.idRegister=r.idRegister "
					+ "ORDER BY p.create_date DESC "
					+ "LIMIT 0, ?";
		} else {
			sql = "SELECT p.idPouch, p.pouchImg, p.pouchTitle, p.create_date, r.userType, r.nickName, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.isDisplay, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2 "
					+ "FROM pouch p, register r "
					+ "WHERE p.todayPouch=0 AND p.idRegister=r.idRegister" + where + " "
					+ "ORDER BY p.create_date DESC "
					+ "LIMIT 0, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, count);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<PouchCMD>();
				
				while(rs.next()) {
					int idPouch = rs.getInt(1);
					int pouchImg = rs.getInt(2);
					String pouchTitle = rs.getString(3);
					String create_date = rs.getString(4);
					String userType = rs.getString(5);
					String nickName = rs.getString(6);
					int count1 = rs.getInt(7);
					int isDisplay = rs.getInt(8);
					int recommendCount = rs.getInt(9);
					int commentCount = rs.getInt(10);
					
					FileInfo image = this.getImage(idPouch);
					
					PouchCMD item = new PouchCMD(idPouch, nickName, userType, pouchTitle, isDisplay, pouchImg, create_date, image, count1, recommendCount, commentCount);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getTodayPouchList] " + e.getLocalizedMessage());
			logger.error("[getTodayPouchList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getTodayPouchList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getTodayPouchList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 픽리스트 xml */
	public ArrayList<PouchCMD> getPickListXml(int page, int count, String order, String where) throws SQLException {
		logger.debug("[getPickListXml] ---------- Method Start ----------");
		
		ArrayList<PouchCMD> result = null;
		
		int cnt = db.getCount("(SELECT p.idPouch, pc.pouchCategoryText, p.idBrand, b.brandTitle, p.pouchTitle, p.pouchText, p.editerPick, p.create_date, r.nickName, r.idRegister, r.userType, r.birthYear, c.commonText, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE c.isDisplay=1 AND p.idPouch=c.idPouch) count2, p.readCount, p.pouchImg "
					+ "FROM pouch p INNER JOIN register r ON p.idRegister=r.idRegister AND p.isDisplay=1 "
					+ "LEFT OUTER JOIN commoncode c ON r.skinType=c.idCommon "
					+ "LEFT OUTER JOIN pouchcategory pc ON p.idPouchCategory=pc.idPouchCategory "
					+ "LEFT OUTER JOIN brand b ON p.idBrand=b.idBrand "
					+ "LEFT OUTER JOIN pouchproductmapping m ON m.idPouch=p.idPouch "
					+ "LEFT OUTER JOIN wishuserpouchmapping wm ON wm.idPouch=p.idPouch "
					+ "WHERE m.idProduct is not null" + where + " "
					+ "GROUP BY p.idPouch) tbl", "*", null);
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = null;
		if (order == null) {
			sql = "SELECT p.idPouch, pc.pouchCategoryText, p.idBrand, b.brandTitle, p.pouchTitle, p.pouchText, p.editerPick, p.create_date, r.nickName, r.idRegister, r.userType, r.birthYear, c.commonText, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE c.isDisplay=1 AND p.idPouch=c.idPouch) count2, p.readCount, p.pouchImg "
					+ "FROM pouch p INNER JOIN register r ON p.idRegister=r.idRegister AND p.isDisplay=1 "
					+ "LEFT OUTER JOIN commoncode c ON r.skinType=c.idCommon "
					+ "LEFT OUTER JOIN pouchcategory pc ON p.idPouchCategory=pc.idPouchCategory "
					+ "LEFT OUTER JOIN brand b ON p.idBrand=b.idBrand "
					+ "LEFT OUTER JOIN pouchproductmapping m ON m.idPouch=p.idPouch "
					+ "LEFT OUTER JOIN wishuserpouchmapping wm ON wm.idPouch=p.idPouch "
					+ "WHERE m.idProduct is not null" + where + " "
					+ "GROUP BY p.idPouch "
					+ "ORDER BY p.create_date DESC "
					+ "LIMIT ?, ?";
		} else {
			sql = "SELECT p.idPouch, pc.pouchCategoryText, p.idBrand, b.brandTitle, p.pouchTitle, p.pouchText, p.editerPick, p.create_date, r.nickName, r.idRegister, r.userType, r.birthYear, c.commonText, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE c.isDisplay=1 AND p.idPouch=c.idPouch) count2, p.readCount, p.pouchImg "
					+ "FROM pouch p INNER JOIN register r ON p.idRegister=r.idRegister AND p.isDisplay=1 "
					+ "LEFT OUTER JOIN commoncode c ON r.skinType=c.idCommon "
					+ "LEFT OUTER JOIN pouchcategory pc ON p.idPouchCategory=pc.idPouchCategory "
					+ "LEFT OUTER JOIN brand b ON p.idBrand=b.idBrand "
					+ "LEFT OUTER JOIN pouchproductmapping m ON m.idPouch=p.idPouch "
					+ "LEFT OUTER JOIN wishuserpouchmapping wm ON wm.idPouch=p.idPouch "
					+ "WHERE m.idProduct is not null" + where + " "
					+ "GROUP BY p.idPouch "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<PouchCMD>();
				
				while(rs.next()) {
					int idPouch = rs.getInt(1);
					String pouchCategoryText = rs.getString(2);
					int idBrand = rs.getInt(3);
					String brandTitle = rs.getString(4);
					String pouchTitle = rs.getString(5);
					String pouchText = rs.getString(6);
					int editerPick = rs.getInt(7);
					String create_date = rs.getString(8);
					String nickName = rs.getString(9);
					int userImg = rs.getInt(10);
					String userType = rs.getString(11);
					int birthYear = rs.getInt(12);
					String skinType = rs.getString(13);
					int productCount = rs.getInt(14);
					int recommendCount = rs.getInt(15);
					int commentCount = rs.getInt(16);
					int readCount = rs.getInt(17);
					int pouchImg = rs.getInt(18);
					
					FileInfo userFile = this.getImage3(userImg, "register");
					FileInfo productFile = this.getImage4(pouchImg);
					FileInfo brandFile = this.getImage3(idBrand, "brand");
					
					PouchCMD item = new PouchCMD(idPouch, userImg, nickName, userType, pouchCategoryText, pouchTitle, pouchText, readCount, 
							recommendCount, editerPick, create_date, productFile, userFile, brandFile, productCount, commentCount, idBrand, 
							brandTitle, birthYear, skinType);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getTodayPouchList] " + e.getLocalizedMessage());
			logger.error("[getTodayPouchList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getTodayPouchList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getTodayPouchList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 픽리스트 xml */
	public PouchCMD getPouchXml(int id) throws SQLException {
		logger.debug("[getPouchXml] ---------- Method Start ----------");
		
		PouchCMD result = null;
		
		String sql = "SELECT p.idPouch, pc.pouchCategoryText, p.idBrand, b.brandTitle, p.pouchTitle, p.pouchText, p.editerPick, p.create_date, r.nickName, r.idRegister, r.userType, r.birthYear, c.commonText, "
					+ "(SELECT COUNT(m.idProduct) FROM pouchproductmapping m WHERE p.idPouch=m.idPouch) count1, p.recommendCount, "
					+ "(SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) count2, p.readCount, p.pouchImg "
					+ "FROM pouch p INNER JOIN register r ON p.idRegister=r.idRegister AND p.isDisplay=1 "
					+ "LEFT OUTER JOIN commoncode c ON r.skinType=c.idCommon "
					+ "LEFT OUTER JOIN pouchcategory pc ON p.idPouchCategory=pc.idPouchCategory "
					+ "LEFT OUTER JOIN brand b ON p.idBrand=b.idBrand "
					+ "LEFT OUTER JOIN pouchproductmapping m ON m.idPouch=p.idPouch "
					+ "LEFT OUTER JOIN wishuserpouchmapping wm ON wm.idPouch=p.idPouch "
					+ "WHERE p.idPouch=? LIMIT 0, 1";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, id);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				
				if(rs.next()) {
					int idPouch = rs.getInt(1);
					String pouchCategoryText = rs.getString(2);
					int idBrand = rs.getInt(3);
					String brandTitle = rs.getString(4);
					String pouchTitle = rs.getString(5);
					String pouchText = rs.getString(6);
					int editerPick = rs.getInt(7);
					String create_date = rs.getString(8);
					String nickName = rs.getString(9);
					int userImg = rs.getInt(10);
					String userType = rs.getString(11);
					int birthYear = rs.getInt(12);
					String skinType = rs.getString(13);
					int productCount = rs.getInt(14);
					int recommendCount = rs.getInt(15);
					int commentCount = rs.getInt(16);
					int readCount = rs.getInt(17);
					int pouchImg = rs.getInt(18);
					
					FileInfo userFile = this.getImage3(userImg, "register");
					FileInfo productFile = this.getImage4(pouchImg);
					FileInfo brandFile = this.getImage3(idBrand, "brand");
					
					result = new PouchCMD(idPouch, userImg, nickName, userType, pouchCategoryText, pouchTitle, pouchText, readCount, 
							recommendCount, editerPick, create_date, productFile, userFile, brandFile, productCount, commentCount, idBrand, 
							brandTitle, birthYear, skinType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getPouchXml] " + e.getLocalizedMessage());
			logger.error("[getPouchXml] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		logger.debug("[getPouchXml] ---------- Method End ----------");
		
		return result;
	}
	
	public ArrayList<PouchCMD> getEditerPickList(int page, int count) throws Exception {
		logger.debug("[getEditerPickList] ---------- Method Start ----------");
		logger.debug("[getEditerPickList][PARAM] page=" + page);
		logger.debug("[getEditerPickList][PARAM] count=" + count);
		
		ArrayList<PouchCMD> result = null;
		int cnt = db.getCount("pouch", "idPouch", "editerPick=1");
		
		String sql = "SELECT idPouch, pouchTitle, start_date, end_date "
					+ "FROM pouch "
					+ "WHERE editerPick=1 "
					+ "ORDER BY modified_date DESC "
					+ "LIMIT ?, ?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<PouchCMD>();
				
				while(rs.next()) {
					int idPouch = rs.getInt(1);
					String pouchTitle = rs.getString(2);
					String start_date = rs.getString(3);
					String end_date = rs.getString(4);
					PouchCMD item = new PouchCMD(idPouch, pouchTitle, start_date, end_date);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getEditerPickList] " + e.getLocalizedMessage());
			logger.error("[getEditerPickList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getEditerPickList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getEditerPickList] ---------- Method End ----------");
		
		return result;
	}
	
	public FileInfo getImage(int idx) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
				+ "FROM attachefile "
				+ "WHERE tableName='pouch' AND tableKey=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo info = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return info;
	}
	
	public FileInfo getImage2(int idx) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
				+ "FROM attachefile "
				+ "WHERE idAttacheFile=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo info = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return info;
	}
	
	public FileInfo getImage3(int idx, String table) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
				+ "FROM attachefile "
				+ "WHERE tableName='" + table + "' AND tableKey=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo info = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return info;
	}
	
	public FileInfo getImage4(int idx) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
				+ "FROM attachefile "
				+ "WHERE idAttacheFile=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo info = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return info;
	}
	
	public ArrayList<PouchProductMapCMD> getPouchProductMap(int idx) throws SQLException {
		
		ArrayList<PouchProductMapCMD> result = null;
		
		String sql = "SELECT m.idPouchProduct, b.idBrand, m.idProduct "
				+ "FROM pouchproductmapping m "
				+ "LEFT OUTER JOIN product p ON m.idProduct=p.idProduct "
				+ "LEFT OUTER JOIN brand b ON b.idBrand=p.idBrand "
				+ "WHERE m.idPouch=? "
				+ "GROUP BY m.idPouchProduct";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				result = new ArrayList<PouchProductMapCMD>();
				while(rs.next()) {
					int idPouchProduct = rs.getInt(1);
					int idBrand = rs.getInt(2);
					int idProduct = rs.getInt(3);
					
					FileInfo image = this.getImage3(idProduct, "product");
					PouchProductMapCMD cmd = new PouchProductMapCMD(idPouchProduct, idBrand, idProduct, image);
					result.add(cmd);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		return result;
	}
	
	public ArrayList<ProductCMD> getPouchProductXml(int idx) throws SQLException {
		
		ArrayList<ProductCMD> result = null;
		
		int cnt = db.getCount("product p, brand b, pouch ph, pouchproductmapping m", "*", 
				"p.idBrand=b.idBrand AND ph.idPouch=m.idPouch AND m.idProduct=p.idProduct AND ph.idPouch=" + idx);
		
		String sql = "SELECT p.idProduct, p.productTitle, b.brandTitle, p.volume, p.price, p.productText "
				+ "FROM product p, brand b, pouch ph, pouchproductmapping m "
				+ "WHERE p.idBrand=b.idBrand AND ph.idPouch=m.idPouch AND m.idProduct=p.idProduct AND ph.idPouch=? "
				+ "ORDER BY m.idPouchProduct ASC";
		
		PageHandler.getInstance(1, cnt);
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				result = new ArrayList<ProductCMD>();
				while(rs.next()) {
					int idProduct = rs.getInt(1);
					String productTitle = rs.getString(2);
					String brandTitle = rs.getString(3);
					String volume = rs.getString(4);
					int price = rs.getInt(5);
					String productText = rs.getString(6);
					
					FileInfo productImg = this.getImage3(idProduct, "product");
					
					ProductCMD cmd = new ProductCMD(idProduct, brandTitle, productTitle, volume, price, productText, productImg);
					result.add(cmd);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return result;
	}
	
	public ArrayList<ProductCMD> getPouchProductEditXml(int idx) throws SQLException {
		
		ArrayList<ProductCMD> result = null;
		
		String sql = "SELECT idProduct "
				+ "FROM pouchproductmapping "
				+ "WHERE idPouch=? "
				+ "ORDER BY idPouchProduct ASC";
		
		PageHandler.getInstance(1, 10);
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				result = new ArrayList<ProductCMD>();
				while(rs.next()) {
					int idProduct = rs.getInt(1);
					
					FileInfo productImg = this.getImage3(idProduct, "product");
					
					ProductCMD cmd = new ProductCMD(idProduct, productImg);
					result.add(cmd);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return result;
	}
	
}
